
package sg.edu.nus.iss.cats.dataaccess.mysql;

import sg.edu.nus.iss.cats.dataaccess.*;
import sg.edu.nus.iss.cats.model.*;

import java.util.*;
import java.sql.*;


public class UserRoleDAOImpl {

    RoleManager roleManager = new RoleManager ();

    public Role[] findRolesForUser (String userId) throws DAOException {
        if (userId == null) {
            throw new DAOException ("Specified User Id is null");
        }

        String sql = "SELECT " + DBConstants.ur_roleId + " FROM " + DBConstants.userRolesTableName + " WHERE " + DBConstants.ur_userId + "='" + userId + "'";

        Connection conn = null;
        Vector v = new Vector();
        try {
            conn = DriverManager.getConnection(DBConstants.dbUrl,
                DBConstants.dbUsername,
                DBConstants.dbPassword);
            System.out.println("Created Connection");

            Statement statement = conn.createStatement();
            System.out.println("Executing query: " + sql);

            ResultSet rs = statement.executeQuery(sql);
            System.out.println("Executed query");

            // Go through the results- there must be at the most one.
            while (rs.next()) {
                System.out.println("Processing row");
                String roleId = rs.getString(DBConstants.ur_roleId);
                Role role = roleManager.findRole (roleId);
                if (role != null) { 
                 v.add (role);
                }
            }

            rs.close();
            statement.close ();
            System.out.println("Retrieved roles- number found: " + v.size());

        } catch (Exception e) {

            // If there is an error, we throw an exception.
            String error = "Error retrieving roles. Nested Exception is: " + e;
            System.out.println (error);
            throw new DAOException (error);

        } finally {
            try {
                System.out.println("Closing connection");
                conn.close();
            } catch (Exception e) {}
        }

        // Convert the Vector to an array of Roles

        Role[] result = new Role[v.size()];
        result = (Role[]) v.toArray (result);
        return (result);

    }



    public void updateRolesForUser (String userId, Role[] roles) throws DAOException {
        if (userId == null) {
            throw new DAOException ("Specified User Id is null");
        }

        String deleteSql = "DELETE FROM " + DBConstants.userRolesTableName + " WHERE " + DBConstants.ur_userId + "='" + userId + "'";
        String insertSql = "INSERT INTO " + DBConstants.userRolesTableName + " (" + DBConstants.ur_userId + "," + DBConstants.ur_roleId +") VALUES ('" + userId + "', ?)";

        Connection conn = null;
        
        try {
            conn = DriverManager.getConnection(DBConstants.dbUrl,
                DBConstants.dbUsername,
                DBConstants.dbPassword);
            System.out.println("Created Connection");

            // Delete the new roles
            Statement st = conn.createStatement();
            System.out.println("Executing delete: " + deleteSql);
            int rows = st.executeUpdate(deleteSql);
            System.out.println("Executed delete"+rows);
            st.close();

            // Now add the new roles
            PreparedStatement pst = conn.prepareStatement(insertSql);

            System.out.println("Executing insert: " + insertSql);
            for (int i = 0; i < roles.length; i++) {
                System.out.println("Role :[ " + i + "]" + roles[i].getRoleId());
                pst.setString(1, roles[i].getRoleId());
                pst.executeUpdate();
                System.out.println("Executed insert");
            }
            pst.close();

           // conn.commit();

        } catch (Exception e) {

            // If there is an error, we throw an exception.
            String error = "Error modifying roles. Nested Exception is: " + e;
            System.out.println (error);
            throw new DAOException (error);

        } finally {
            try {
                System.out.println("Closing connection");
                conn.rollback();
                conn.close();
            } catch (Exception e) {}
        }
    }

}